using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class TVC_RFITMDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procTVC_RFITMPRV_add]";
        private string _strSPUpdateName = "dbo.[procTVC_RFITMPRV_update]";
        private string _strSPDeleteName = "dbo.[procTVC_RFITMPRV_delete]";
        private string _strSPGetName = "dbo.[procTVC_RFITMPRV_get]";
        private string _strSPGetAllName = "dbo.[procTVC_RFITMPRV_getall]";
        private string _strSPGetPages = "dbo.[procTVC_RFITMPRV_getpaged]";
        private string _strSPIsExist = "dbo.[procTVC_RFITMPRV_isexist]";
        private string _strTableName = "[TVC_RFITMPRV]";
        private string _strSPGetTransferOutName = "dbo.[procTVC_RFITMPRV_gettransferout]";
        private string _strSPGetFilter = "[dbo].[procTVC_RFITMPRV_getFilter]";
        private string _strSPUpdateAve_Cost = "dbo.[procTVC_RFITMPRV_updateAve_Cost]";
        #endregion Local Variable

        #region Method
        public TVC_RFITMInfo Get(string db, String ItemCode, ref string sErr)
        {
            string sql = @"SELECT        ItemCode, Description, ShortDescription, UOM, Category, BudgetCode, BudgetYear, PurchaseAccount, AccrualAccount, isLink, Note1, Note2, Note3, Department, Ave_Cost, MINSTOCK, MAXSTOCK, ANAL_I0, 
                         ANAL_I1, ANAL_I2, ANAL_I3, ANAL_I4, ANAL_I5, ANAL_I6, ANAL_I7, ANAL_I8, ANAL_I9
            FROM            TVC_RFITM" + db + @"
            WHERE        (ItemCode = @ItemCode)";
            TVC_RFITMInfo objEntr = new TVC_RFITMInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), ItemCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = new TVC_RFITMInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        ItemCode, Description, ItemCode + ' - ' + Description AS DisplayCBO, ShortDescription, UOM, Category, BudgetCode, BudgetYear, PurchaseAccount, AccrualAccount, isLink, Note1, Note2, Note3, Department, 
                         Ave_Cost, MINSTOCK, MAXSTOCK, ANAL_I0, ANAL_I1, ANAL_I2, ANAL_I3, ANAL_I4, ANAL_I5, ANAL_I6, ANAL_I7, ANAL_I8, ANAL_I9
            FROM            TVC_RFITM" + db + @"
            ORDER BY ItemCode";
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, TVC_RFITMInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO TVC_RFITM" + db + @"
                         (ItemCode, Description, ShortDescription, UOM, Category, BudgetCode, BudgetYear, PurchaseAccount, AccrualAccount, isLink, Note1, Note2, Note3, Department, Ave_Cost, MINSTOCK, MAXSTOCK, ANAL_I0, 
                         ANAL_I1, ANAL_I2, ANAL_I3, ANAL_I4, ANAL_I5, ANAL_I6, ANAL_I7, ANAL_I8, ANAL_I9)
            VALUES        (@ItemCode,@Description,@ShortDescription,@UOM,@Category,@BudgetCode,@BudgetYear,@PurchaseAccount,@AccrualAccount,@isLink,@Note1,@Note2,@Note3,@Department,@Ave_Cost,@MINSTOCK,@MAXSTOCK,@ANAL_I0,@ANAL_I1,@ANAL_I2,@ANAL_I3,@ANAL_I4,@ANAL_I5,@ANAL_I6,@ANAL_I7,@ANAL_I8,@ANAL_I9)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_RFITMInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_RFITMInfo.Field.ShortDescription.ToString(), objEntr.ShortDescription);
            AddParameter(TVC_RFITMInfo.Field.UOM.ToString(), objEntr.UOM);
            AddParameter(TVC_RFITMInfo.Field.Category.ToString(), objEntr.Category);
            AddParameter(TVC_RFITMInfo.Field.BudgetCode.ToString(), objEntr.BudgetCode);
            AddParameter(TVC_RFITMInfo.Field.BudgetYear.ToString(), objEntr.BudgetYear);
            AddParameter(TVC_RFITMInfo.Field.PurchaseAccount.ToString(), objEntr.PurchaseAccount);
            AddParameter(TVC_RFITMInfo.Field.AccrualAccount.ToString(), objEntr.AccrualAccount);
            AddParameter(TVC_RFITMInfo.Field.isLink.ToString(), objEntr.isLink);
            AddParameter(TVC_RFITMInfo.Field.Note1.ToString(), objEntr.Note1);
            AddParameter(TVC_RFITMInfo.Field.Note2.ToString(), objEntr.Note2);
            AddParameter(TVC_RFITMInfo.Field.Note3.ToString(), objEntr.Note3);
            AddParameter(TVC_RFITMInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_RFITMInfo.Field.Ave_Cost.ToString(), objEntr.Ave_Cost);
            AddParameter(TVC_RFITMInfo.Field.MINSTOCK.ToString(), objEntr.MINSTOCK);
            AddParameter(TVC_RFITMInfo.Field.MAXSTOCK.ToString(), objEntr.MAXSTOCK);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I0.ToString(), objEntr.ANAL_I0);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I1.ToString(), objEntr.ANAL_I1);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I2.ToString(), objEntr.ANAL_I2);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I3.ToString(), objEntr.ANAL_I3);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I4.ToString(), objEntr.ANAL_I4);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I5.ToString(), objEntr.ANAL_I5);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I6.ToString(), objEntr.ANAL_I6);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I7.ToString(), objEntr.ANAL_I7);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I8.ToString(), objEntr.ANAL_I8);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I9.ToString(), objEntr.ANAL_I9);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, TVC_RFITMInfo objEntr)
        {
            string sql = @"UPDATE       TVC_RFITM" + db + @"
            SET                Description = @Description, ShortDescription = @ShortDescription, UOM = @UOM, Category = @Category, BudgetCode = @BudgetCode, BudgetYear = @BudgetYear, PurchaseAccount = @PurchaseAccount, 
                         AccrualAccount = @AccrualAccount, isLink = @isLink, Note1 = @Note1, Note2 = @Note2, Note3 = @Note3, Department = @Department, MINSTOCK = @MINSTOCK, MAXSTOCK = @MAXSTOCK, 
                         ANAL_I0 = @ANAL_I0, ANAL_I1 = @ANAL_I1, ANAL_I2 = @ANAL_I2, ANAL_I3 = @ANAL_I3, ANAL_I4 = @ANAL_I4, ANAL_I5 = @ANAL_I5, ANAL_I6 = @ANAL_I6, ANAL_I7 = @ANAL_I7, ANAL_I8 = @ANAL_I8, 
                         ANAL_I9 = @ANAL_I9
            WHERE        (ItemCode = @ItemCode)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_RFITMInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_RFITMInfo.Field.ShortDescription.ToString(), objEntr.ShortDescription);
            AddParameter(TVC_RFITMInfo.Field.UOM.ToString(), objEntr.UOM);
            AddParameter(TVC_RFITMInfo.Field.Category.ToString(), objEntr.Category);
            AddParameter(TVC_RFITMInfo.Field.BudgetCode.ToString(), objEntr.BudgetCode);
            AddParameter(TVC_RFITMInfo.Field.BudgetYear.ToString(), objEntr.BudgetYear);
            AddParameter(TVC_RFITMInfo.Field.PurchaseAccount.ToString(), objEntr.PurchaseAccount);
            AddParameter(TVC_RFITMInfo.Field.AccrualAccount.ToString(), objEntr.AccrualAccount);
            AddParameter(TVC_RFITMInfo.Field.isLink.ToString(), objEntr.isLink);
            AddParameter(TVC_RFITMInfo.Field.Note1.ToString(), objEntr.Note1);
            AddParameter(TVC_RFITMInfo.Field.Note2.ToString(), objEntr.Note2);
            AddParameter(TVC_RFITMInfo.Field.Note3.ToString(), objEntr.Note3);
            AddParameter(TVC_RFITMInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_RFITMInfo.Field.MINSTOCK.ToString(), objEntr.MINSTOCK);
            AddParameter(TVC_RFITMInfo.Field.MAXSTOCK.ToString(), objEntr.MAXSTOCK);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I0.ToString(), objEntr.ANAL_I0);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I1.ToString(), objEntr.ANAL_I1);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I2.ToString(), objEntr.ANAL_I2);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I3.ToString(), objEntr.ANAL_I3);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I4.ToString(), objEntr.ANAL_I4);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I5.ToString(), objEntr.ANAL_I5);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I6.ToString(), objEntr.ANAL_I6);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I7.ToString(), objEntr.ANAL_I7);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I8.ToString(), objEntr.ANAL_I8);
            AddParameter(TVC_RFITMInfo.Field.ANAL_I9.ToString(), objEntr.ANAL_I9);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String ItemCode)
        {
            string sql = @"DELETE FROM TVC_RFITM" + db + @"
            WHERE        (ItemCode = @ItemCode)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), ItemCode);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist(string db, String ItemCode)
        {
            string sql = @"SELECT        TOP (1) ItemCode
            FROM            TVC_RFITM" + db + @"
            WHERE        (ItemCode = @ItemCode)";
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), ItemCode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(TVC_RFITMInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method



        public DataTable GetFilter(string db, string strFilter, ref string sErr)
        {
            string sql = @"select * FROM  TVC_RFITM" + db;
            if (!string.IsNullOrEmpty(strFilter)) sql += " WHERE " + strFilter;
            sql += " ORDER BY ItemCode ";

            TVC_RFITMInfo objEntr = new TVC_RFITMInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter("strFilter", strFilter);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


        public void Update(string db, string aItem, double aAve_Cost, ref string sErr)
        {
            string sql = @"UPDATE       TVC_RFITM" + db + @"
            SET                Ave_Cost = @Ave_Cost
            WHERE        (ItemCode = @ItemCode)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_RFITMInfo.Field.ItemCode.ToString(), aItem);
            AddParameter(TVC_RFITMInfo.Field.Ave_Cost.ToString(), aAve_Cost);
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

        }


        public DataTable GetModelPN(string db, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT DISTINCT  ANAL_I4
                FROM TVC_RFITM" + db, CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public bool DesIsExist(string db, string Description, string ItemCode)
        {
            InitConnect();
            InitCommand(@"SELECT TOP(1) Description FROM TVC_RFITM" + db + @"
                WHERE (Description = @Description AND ItemCode <> @ItemCode)", CommandType.Text);

            AddParameter("Description", Description);
            AddParameter("ItemCode", ItemCode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list.Rows.Count > 0;
        }

        public bool ANAL_I4IsExist(string db, string ANAL_I4, string ItemCode)
        {
            InitConnect();
            InitCommand(@"SELECT TOP(1) ANAL_I4 FROM TVC_RFITM" + db + @"
                WHERE (ANAL_I4 = @ANAL_I4 AND ItemCode <> @ItemCode)", CommandType.Text);

            AddParameter("ANAL_I4", ANAL_I4);
            AddParameter("ItemCode", ItemCode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list.Rows.Count > 0;
        }
    }
}
